# import cx_Oracle
import pymysql
import datetime
from django.conf import settings

class DB(object):
    """
            數據庫操作類
    """
    def __init__(self,user,password,host,port,dbname,sid=None):
        self.user = user
        self.password = password
        self.host = host
        self.port = port
        self.dbname = dbname
        self.sid = sid
        self.charset = 'utf8'
        self.conn = None
        self.cur = None

    def get_conn(self):
        if self.dbname == 'mysql':

            return pymysql.connect(host=self.host,port=self.port,user=self.user,password=self.password,db=self.sid,charset=self.charset)
        elif self.dbname == 'oracle':
            pass
            # return cx_Oracle.connect(self.user+'/'+self.password+'@'+self.host+':'+self.port+'/'+self.sid)


    def get_cur(self):
        """
           數據庫連接
        """
        try:
            self.conn = self.get_conn()
            self.cur = self.conn.cursor()
        except Exception as e:
            print('Cursor Error',e)

    def close(self):
        """
            關閉數據庫連接
        """
        self.cur.close()
        self.conn.close()

    def query(self,sql):
        """
            查詢
        """
        try:
            self.cur.execute(sql)
            data = self.cur.fetchall()
            return data
        except Exception as e:
            print('Query Error:',e)

    def queryone(self,sql):
        """
            查詢
        """
        try:
            self.cur.execute(sql)
            data = self.cur.fetchone()
            return data
        except Exception as e:
            print('Query Error:',e)

    def insert(self,sql):
        """
            插入，更新
        """
        try:
            self.cur.execute(sql)
            self.conn.commit()
        except Exception as e:
            print('Insert Error',e)


    def callSP(self,spName,cursor):
        """
            Call 存儲過程
        """
        try:
            data = self.cur.callproc(spName,cursor)
            return data
        except Exception as e:
            print('SP Call Error:',e)

from wfs import models
def geshihua(sql,type,page,num):
    dblist =settings.DATABASES['default']
    con = DB(user=dblist['USER'],password=dblist['PASSWORD'],host=dblist['HOST'],port=int(dblist['PORT']),sid=dblist['NAME'],dbname='mysql')
    con.get_cur()
    data = con.query(sql)
    con.close()
    content={}
    list1=[]
    list = []
    if data !=None:
        for i in data:
            content={}
            if i[1] in list:
                index = list.index(i[1])
                name = models.User.objects.filter(username=i[8]).values('last_name')[0]['last_name']

                if i[12] == '0':
                    list1[index]['approver_name'].append(i[8]+'('+name+')')
                if  len(list1[index]['approver_name'])>1:
                    if '已簽核' in list1[index]['approver_name']:
                        list1[index]['approver_name'].remove('已簽核')
                    elif '轉呈' in list1[index]['approver_name']:
                        list1[index]['approver_name'].remove('轉呈')
                    elif '發起' in list1[index]['approver_name']:
                        list1[index]['approver_name'].remove('發起')

            else:
                name = models.User.objects.filter(username=i[8]).values('last_name')
                if name:
                    name = i[8]+'('+name[0]['last_name']+')'
                content['approver_name'] =[]
                content['card_id']=i[0]
                content['id'] = i[1]
                content['name']=i[2]
                content['apply']={'applicant_id':i[3],'applicant_name':i[10]}
                content['wrecord_id']=i[4]
                content['wrecord__create_time']=i[5].strftime("%Y-%m-%d %H:%M:%S")
                content['post_type']=i[6]
                action = i[13]
                # print(i[5],i[9])
                if i[13] == '通過':
                    action = '已簽核'

                content['approver_name'] = [name] if i[12] == '0' else [action]
                content['approver']= i[8]
                # content['action'] = action
                if int(i[11])==0:
                    content['action'] = '未簽核'
                    # content['wrecord__end_time'] = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                    content['wrecord__end_time'] =None
                    # print(content['wrecord__end_time'])
                    t = datetime.datetime.now() - i[5]
                else:
                    content['action'] = '已簽核'
                    content['wrecord__end_time'] = i[9]

                    t = datetime.datetime.now()- i[5]
                if not int(type):
                    if i[3] == i[8]:
                        content['return']=1
                    else:
                        content['return']=0
                else:
                    content['return']= 0

                sec = int(t.seconds)+int(t.days)*24*3600
                content['timestamp'] = sec
                hour = int(sec/ 3600)
                # print(t)
                # print(t.strftime('%H:%M:%S'))
                minu = int((sec - hour * 3600) / 60)
                if hour == 0:
                    content['time'] = str(minu) + '分鐘'
                else:
                    content['time'] = str(hour) + '小時' + str(minu) + '分鐘'
                list1.append(content)
                if i[1] not in list:
                    list.append(i[1])
        total = len(list1)

        t_page = int(total/num) if total % num == 0 else int(total/num)+1
        p_begin = (page-1)*20
        p_end = page*20
        if  page ==t_page:
            page_content = list1[p_begin:]

        else:
            page_content=list1[p_begin:p_end]
        # print(total, len(page_content))



    return [page_content,t_page,page,total]
